********************************************************************************
** 	TITLE:		NI13_results                                                  ** 	
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       wahlergebnis                                                  **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Import the dataset

copy https://www.aktuelle-wahlen-niedersachsen.de/LW2013/wahlergebnis.csv wahlergebnis.csv, replace
type wahlergebnis.csv
import delimited wahlergebnis.csv

drop v39

* Rename variables

rename wahlkreis districtnumber13
rename wãhler wälher
rename gãltigeerststimmen gültigeerststimmen
rename gãltigezweitstimmen gültigezweitstimmen
rename bãndnis21rrpi bündnis21rrpi
rename bãndnis21rrpii bündnis21rrpii
rename grãnei grünei
rename grãneii grüneii
rename freiewãhleri freiewähleri
rename freiewãhlerii freiewählerii
rename gültigeerststimmen numberofvalidvotes13

* Reshape the dataset

rename cdui v13_1_cdu_i
rename cduii v13_2_cdu_ii
rename spdi v13_1_spd_i
rename spdii v13_2_spd_ii
rename fdpi v13_1_fdp_i
rename fdpii v13_2_fdp_ii
rename grünei v13_1_grüne_i
rename grüneii v13_2_grüne_ii
rename dielinkei v13_1_dielinke_i
rename dielinkeii v13_2_dielinke_ii
rename bündnis21rrpi v13_1_bündnis21rrp_i
rename bündnis21rrpii v13_2_bündnis21rrp_ii
rename ddpi v13_1_ddp_i
rename ddpii v13_2_ddp_ii
rename zentrumi v13_1_zentrum_i
rename zentrumii v13_2_zentrum_ii
rename diefreiheitniedersachseni v13_1_diefreiheit_i
rename diefreiheitniedersachsenii v13_2_diefreiheit_ii
rename familiei v13_1_familie_i
rename familieii v13_2_familie_ii
rename freiewähleri v13_1_freiewähler_i
rename freiewählerii v13_2_freiewähler_ii
rename mdui v13_1_mdu_i
rename mduii v13_2_mdu_ii
rename npdi v13_1_npd_i
rename npdii v13_2_npd_ii
rename neini v13_1_nein_i
rename neinii v13_2_nein_ii
rename pbci v13_1_pbc_i
rename pbcii v13_2_pbc_ii
rename pirateni v13_1_piraten_i
rename piratenii v13_2_piraten_ii
rename einzelbewerber1 v13_1_einzelbewerber1

reshape long v13_1_ v13_2_, i(districtnumber13) j(var) string

* Drop second vote

drop if strpos(var, "_ii") > 0
drop v13_2_

* Generate dichotomous variable for each party 

gen cdu13 = strpos(var, "cdu_i") > 0
gen spd13 = strpos(var, "spd_i") > 0
gen fdp13 = strpos(var, "fdp_i") > 0
gen grune13 = strpos(var, "grüne_i") > 0
gen dielinke13 = strpos(var, "dielinke_i") > 0
gen bundnis13 = strpos(var, "bündnis21rrp_i") > 0
gen ddp13 = strpos(var, "ddp_i") > 0
gen zentrum13 = strpos(var, "zentrumi") > 0
gen diefreiheit13 = strpos(var, "diefreiheit_i") > 0
gen familie13 = strpos(var, "familie_i") > 0
gen freiewahler13 = strpos(var, "freiewähler_i") > 0
gen mdu13 = strpos(var, "mdu_i") > 0
gen npd13 = strpos(var, "npd_i") > 0
gen nein13 = strpos(var, "nein_i") > 0
gen pbc13 = strpos(var, "pbc_i") > 0
gen piraten13 = strpos(var, "piraten_i") > 0
gen einzelbewerber13 = strpos(var, "einzelbewerber_1") > 0

* Generate party variable

gen party13 = 1 if cdu13 == 1
replace party13 = 2 if spd13 == 1
replace party13 = 3 if grune13 == 1
replace party13 = 4 if fdp13 == 1
replace party13 = 5 if dielinke13 == 1
replace party13 = 6 if piraten13 == 1
replace party13 = 88 if party13 == . 

* Generate district names

gen districtname13 = ""

replace districtname13 = "Braunschweig-Nord" if districtnumber13 == 1
replace districtname13 = "Braunschweig-Süd" if districtnumber13 == 2
replace districtname13 = "Braunschweig-West" if districtnumber13 == 3
replace districtname13 = "Peine" if districtnumber13 == 4
replace districtname13 = "Gifhorn-Nord/Wolfsburg" if districtnumber13 == 5
replace districtname13 = "Gifhorn-Süd" if districtnumber13 == 6
replace districtname13 = "Wolfsburg" if districtnumber13 == 7
replace districtname13 = "Helmstedt" if districtnumber13 == 8
replace districtname13 = "Wolfenbüttel-Nord" if districtnumber13 == 9
replace districtname13 = "Wolfenbüttel-Süd/Salzgitter" if districtnumber13 == 10
replace districtname13 = "Salzgitter" if districtnumber13 == 11
replace districtname13 = "Osterode" if districtnumber13 == 12
replace districtname13 = "Seesen" if districtnumber13 == 13
replace districtname13 = "Goslar" if districtnumber13 == 14
replace districtname13 = "Duderstadt" if districtnumber13 == 15
replace districtname13 = "Göttingen/Münden" if districtnumber13 == 16
replace districtname13 = "Göttingen-Stadt" if districtnumber13 == 17
replace districtname13 = "Northeim" if districtnumber13 == 18
replace districtname13 = "Einbeck" if districtnumber13 == 19
replace districtname13 = "Holzminden" if districtnumber13 == 20
replace districtname13 = "Hildesheim" if districtnumber13 == 21
replace districtname13 = "Sarstedt/Bad Salzdetfurth" if districtnumber13 == 22
replace districtname13 = "Alfeld" if districtnumber13 == 23
replace districtname13 = "Hannover-Döhren" if districtnumber13 == 24
replace districtname13 = "Hannover-Buchholz" if districtnumber13 == 25
replace districtname13 = "Hannover-Linden" if districtnumber13 == 26
replace districtname13 = "Hannover-Ricklingen" if districtnumber13 == 27
replace districtname13 = "Hannover-Mitte" if districtnumber13 == 28
replace districtname13 = "Laatzen" if districtnumber13 == 29
replace districtname13 = "Lehrte" if districtnumber13 == 30
replace districtname13 = "Langenhagen" if districtnumber13 == 31
replace districtname13 = "Garbsen/Wedemark" if districtnumber13 == 32
replace districtname13 = "Neustadt/Wunstorf" if districtnumber13 == 33
replace districtname13 = "Barsinghausen" if districtnumber13 == 34
replace districtname13 = "Springe" if districtnumber13 == 35
replace districtname13 = "Bad Pyrmont" if districtnumber13 == 36
replace districtname13 = "Schaumburg" if districtnumber13 == 37
replace districtname13 = "Hameln/Rinteln" if districtnumber13 == 38
replace districtname13 = "Nienburg/Schaumburg" if districtnumber13 == 39
replace districtname13 = "Nienburg-Nord" if districtnumber13 == 40
replace districtname13 = "Syke" if districtnumber13 == 41
replace districtname13 = "Diepholz" if districtnumber13 == 42
replace districtname13 = "Walsrode" if districtnumber13 == 43
replace districtname13 = "Soltau" if districtnumber13 == 44
replace districtname13 = "Bergen" if districtnumber13 == 45
replace districtname13 = "Celle" if districtnumber13 == 46
replace districtname13 = "Uelzen" if districtnumber13 == 47
replace districtname13 = "Elbe" if districtnumber13 == 48
replace districtname13 = "Lüneburg" if districtnumber13 == 49
replace districtname13 = "Winsen" if districtnumber13 == 50
replace districtname13 = "Seevetal" if districtnumber13 == 51
replace districtname13 = "Buchholz" if districtnumber13 == 52
replace districtname13 = "Rotenburg" if districtnumber13 == 53
replace districtname13 = "Bremervörde" if districtnumber13 == 54
replace districtname13 = "Buxtehude" if districtnumber13 == 55
replace districtname13 = "Stade" if districtnumber13 == 56
replace districtname13 = "Hadeln/Wesermünde" if districtnumber13 == 57
replace districtname13 = "Cuxhaven" if districtnumber13 == 58
replace districtname13 = "Unterweser" if districtnumber13 == 59
replace districtname13 = "Osterholz" if districtnumber13 == 60
replace districtname13 = "Verden" if districtnumber13 == 61
replace districtname13 = "Oldenburg-Mitte/Süd" if districtnumber13 == 62
replace districtname13 = "Oldenburg-Nord/West" if districtnumber13 == 63
replace districtname13 = "Oldenburg-Land" if districtnumber13 == 64
replace districtname13 = "Delmenhorst" if districtnumber13 == 65
replace districtname13 = "Cloppenburg-Nord" if districtnumber13 == 66
replace districtname13 = "Cloppenburg" if districtnumber13 == 67
replace districtname13 = "Vechta" if districtnumber13 == 68
replace districtname13 = "Wilhelmshaven" if districtnumber13 == 69
replace districtname13 = "Friesland" if districtnumber13 == 70
replace districtname13 = "Wesermarsch" if districtnumber13 == 71
replace districtname13 = "Ammerland" if districtnumber13 == 72
replace districtname13 = "Bersenbrück" if districtnumber13 == 73
replace districtname13 = "Melle" if districtnumber13 == 74
replace districtname13 = "Bramsche" if districtnumber13 == 75
replace districtname13 = "Georgsmarienhütte" if districtnumber13 == 76
replace districtname13 = "Osnabrück-Ost" if districtnumber13 == 77
replace districtname13 = "Osnabrück-West" if districtnumber13 == 78
replace districtname13 = "Grafschaft Bentheim" if districtnumber13 == 79
replace districtname13 = "Lingen" if districtnumber13 == 80
replace districtname13 = "Meppen" if districtnumber13 == 81
replace districtname13 = "Papenburg" if districtnumber13 == 82
replace districtname13 = "Leer" if districtnumber13 == 83
replace districtname13 = "Leer/Borkum" if districtnumber13 == 84
replace districtname13 = "Emden/Norden" if districtnumber13 == 85
replace districtname13 = "Aurich" if districtnumber13 == 86
replace districtname13 = "Wittmund/Inseln" if districtnumber13 == 87

* Generate vote percentages

rename v13_1_ numberofvotes13

gsort districtname13 -numberofvotes13

bysort districtname13 : gen rank13 = _n

gen numberofvotespercent13 = (numberofvotes13/numberofvalidvotes13)*100

drop if numberofvotespercent13 == .

* Generate rank of parties

gen first13 = party13 if rank13 == 1
gen second13 = party13 if rank13 == 2
gen third13 = party13 if rank13 == 3

bysort districtname13 : gen winner13 = first13[1]
bysort districtname13 : gen runnerup13 = second13[2]
bysort districtname13 : gen thirdplace13 = third13[3]

* Generate margins of victory

bysort districtname13 : gen margin13 = numberofvotespercent13[1] - numberofvotespercent13[2]

* Labelling parties

label define party 1 "CDU/CSU" 2 "SPD" 3 "Green" 4 "FDP" 5 "Die Linke" 6 "Piratenpartei" 88 "Other"
label values winner13 runnerup13 thirdplace13 party

* Selecting, ordering, and sorting variables

keep districtnumber13 districtname13 party13 rank13 numberofvotes13 numberofvotespercent13 margin13 winner13 runnerup13 thirdplace13

order districtnumber13 districtname13 party13 rank13 numberofvotes13 numberofvotespercent13 margin13 winner13 runnerup13 thirdplace13

sort districtnumber13 rank13

drop if rank13 > 3

* Drop duplicates

duplicates tag districtnumber13, gen(dup)
duplicates drop districtnumber13, force
drop dup

* Saving file

save lowersaxony2013_results, replace